SQL is the most commonly used language for querying relational databases. Queries written in SQL can make use of User-Defined Functions. User-defined functions are often written using a mix of imperative programming language constructs and SQL. The performance of a query containing a user-defined functions can be greatly improved by transforming the query into another equivalent query in which the computation done by imperative statements inside the user-defined function is expressed as set-oriented SQL operations, and the per-row execution of queries inside the function body are transformed into set-oriented execution. The process of transforming a query in this way is termed “query unnesting” or “query decorrelation.”
Methods for query unnesting have been developed for nested SQL queries. Nested SQL queries are queries that contain a sub-query. However, these conventional unnesting techniques developed for nested SQL queries cannot be applied for queries containing user-defined functions, which may contain application logic expressed using imperative language constructs along with multiple parameterized queries.
Queries containing user-defined functions can be manually unnested or decorrelated. But, such manual unnesting is time consuming and error prone.
Queries containing user-defined functions may be nested queries with complex inner (sub-query) blocks. In the case of nested queries, the inner block is simply another SQL query with correlation variables used as its parameters. However, UDFs often use a mix of imperative language constructs and SQL, and queries inside UDFs are embedded inside procedural code. There has been a lot of work on efficient evaluation of nested queries by unnesting or decorrelating nested queries. However, these conventional query unnesting techniques cannot be used to unnest queries having UDFs, except for very simple ones (with no imperative constructs).